﻿CREATE PROCEDURE [dbo].[proc_BaiDuShiChang_Customer_Getlist_Remote]
(
		@CompanyId int,
		@SourceId int,
		@IndustryOneId int,
		@IndustryTwoId int,
		@ImportType int,
		@StartDate varchar(10),
		@EndDate varchar(10),
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int
)
AS
Begin
--创建linkServer
--exec sp_addlinkedserver 'sv_link','','SQLOLEDB','106.120.186.110,31000'

--登陆linkServer
--exec sp_addlinkedsrvlogin 'sv_link','false',null,'NEW_0322','lcERP&2012'

--放开远程服务器的存储过程权限
--EXEC master.dbo.sp_serveroption @server=N'sv_link', @optname=N'rpc', @optvalue=N'true'  
--EXEC master.dbo.sp_serveroption @server=N'sv_link', @optname=N'rpc out', @optvalue=N'true'  

--查询
--select Count(0) from sv_link.[24erp].dbo.customer Where companyid=22

--以后不再使用时删除链接服务器
--exec sp_dropserver 'sv_link','droplogins' 

DECLARE @cSql nvarchar(4000),@cWhere nvarchar(2000)
set @cSql = ''
set @cWhere = ''

If @CompanyId>0
	Set @cWhere='CompanyId='+Convert(varchar(10),@CompanyId)
Else
	Set @cWhere='(CompanyId=0)'

If @ImportType = 0
	Set @cWhere = @cWhere + ' And CreateDate>=@sRq And CreateDate<=@eRq And ProtectId=1'

If @SourceId>0
	Set @cWhere=@cWhere+' And SourceId='+Convert(varchar(10),@SourceId)

If @IndustryOneId>0
Begin
	If @IndustryTwoId>0
		Set @cWhere=@cWhere+' And IndustryTwoId='+Convert(varchar(10),@IndustryTwoId)
	Else If @IndustryTwoId=-1
		Set @cWhere=@cWhere+' And IndustryOneId='+Convert(varchar(10),@IndustryOneId)+' And IndustryTwoId Not In(Select Id From Customer_Industry Where FId='+Convert(varchar(10),@IndustryOneId)+')'
	Else
		Set @cWhere=@cWhere+' And IndustryOneId='+Convert(varchar(10),@IndustryOneId)
End

If @IndustryOneId=-1
	Set @cWhere=@cWhere+' And IndustryOneId Not In (Select Id From Customer_Industry Where FId=0)'

If @Stext<>''
	Set @cWhere=@cWhere+' And CustomerName like ''%'+@Stext+'%'''

Set @cSql='Declare @sRq Datetime,@eRq Datetime
Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')'

If @ImportType = 0
	Set @cSql=@cSql+'
	;WITH List As(Select ROW_NUMBER() OVER (ORDER BY CreateDate) AS Row,
	Id,CustomerId,CustomerName,ProtectId,IndustryOneId,IndustryTwoId,SourceId,CreateDate
	From Customer
	Where '+@cWhere+')

	Select *,
	IsNull((Select Title From Customer_Industry Where FId=0 And Id=list.IndustryOneId),''...'') As IndustryOne,
	IsNull((Select Title From Customer_Industry Where FId=list.IndustryOneId And Id=list.IndustryTwoId),''...'') As IndustryTwo,
	IsNull((Select Title From Customer_Source Where Id=list.SourceId),''...'') As Source,
	IsNull((Select Title From Customer_Protect Where Id=list.ProtectId),''...'') As Protect,
	(Select Count(0) From List) As RecordCount,
	IsNull((Select Count(0) From [Order] Where CusId=list.CustomerId And TypeId=1),0) As OrderCount,
	IsNull((Select Sum(YingShouPrice) From [Order] Where CusId=list.CustomerId And TypeId=1),0) As YingShouPrice
	From list
	Where Row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex) + '
	Order By Row'
Else
	Set @cSql=@cSql+'
	;WITH List2 As (
		Select CusId,YingShouPrice From [Order]
		Where CompanyId='+Convert(varchar(10),@CompanyId)+'
		And CreateDate>=@sRq And CreateDate<=@eRq And TypeId=1
	),
	List As (Select ROW_NUMBER() OVER (ORDER BY CreateDate) AS Row,
		Id,CustomerId,CustomerName,ProtectId,IndustryOneId,IndustryTwoId,SourceId,CreateDate,List2.YingShouPrice
		From Customer,List2
		Where '+@cWhere+' And Customer.CustomerId=List2.CusId
	)

	Select *,
	IsNull((Select Title From Customer_Industry Where FId=0 And Id=list.IndustryOneId),''...'') As IndustryOne,
	IsNull((Select Title From Customer_Industry Where FId=list.IndustryOneId And Id=list.IndustryTwoId),''...'') As IndustryTwo,
	IsNull((Select Title From Customer_Source Where Id=list.SourceId),''...'') As Source,
	IsNull((Select Title From Customer_Protect Where Id=list.ProtectId),''...'') As Protect,
	(Select Count(0) From List) As RecordCount,
	1 As OrderCount
	From list
	Where Row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex) + '
	Order By Row'
Exec(@cSql)
--Select @cSql
End
